#!/usr/bin/env python
#-*- encoding:UTF-8 -*-

# $Id$

import string
import wx
import connDB
import adodbapi

import NewVariable

typelst = ["Char", "Num"]

# TO DO
# BrowseFormat
# ValueListDefOID
# ComputationMethodOID

class BrowseFormat(wx.Dialog):
    
    """
    Create a New Dataset in SDS
    """
    
    def __init__(
            self, parent, title="Browse Format: Please select a format!", size=(400,300), pos=wx.DefaultPosition, 
            style=wx.DEFAULT_DIALOG_STYLE
            ):
        
        [wxID_Dataset,
         wxID_Class,
         wxID_Purpose,
         wxID_Version,
         wxID_OK,
         wxID_CANCEL,
         wxID_Note
        ] = [wx.NewId() for _init_ctrls in range(7)]
        
        wx.Dialog.__init__(self, parent, id=wx.ID_ANY, title=title, size=size, pos=pos, style=style)
        
        panel = wx.Panel(self, id=wx.ID_ANY)
        
        
        self.lb1 = wx.StaticText(panel, wx.ID_ANY, "Dataset:")
        self.te1 = wx.TextCtrl(panel, wxID_Dataset, "", size=(130, -1))
        #self.te1.SetMaxLength(6)
        self.te1.SetMaxLength(2)
        
        self.lb2 = wx.StaticText(panel, wx.ID_ANY, "Class:")
        self.te2 = wx.ComboBox(panel, wxID_Class, size=(160, -1))
        
        #to-do: Purpose list
        purposelst=["Tabulation"]
        self.lb3 = wx.StaticText(panel, wx.ID_ANY, "Purpose:")
        self.te3 = wx.ComboBox(panel, wxID_Purpose, "Tabulation", size=(130, -1), choices=purposelst)
        
        #to-do: Version list
        versionlst=["3.1.1"]
        self.lb4 = wx.StaticText(panel, wx.ID_ANY, "Version:")
        self.te4 = wx.ComboBox(panel, wxID_Version, "3.1.1", size=(130, -1), choices=versionlst)
        
        
        #FlexGridSizer
        fgs = wx.FlexGridSizer(4,2,5,5)
        fgs.AddMany([(self.lb1, 0, wx.ALIGN_RIGHT | wx.LEFT | wx.TOP, 5), (self.te1, 0, wx.EXPAND | wx.LEFT | wx.RIGHT | wx.TOP, 5),
                     (self.lb2, 0, wx.ALIGN_RIGHT | wx.LEFT | wx.TOP, 5), (self.te2, 1, wx.EXPAND | wx.LEFT | wx.RIGHT | wx.TOP, 5),
                     (self.lb3, 0, wx.ALIGN_RIGHT | wx.LEFT | wx.TOP, 5), (self.te3, 1, wx.EXPAND | wx.LEFT | wx.RIGHT | wx.TOP, 5),
                     (self.lb4, 0, wx.ALIGN_RIGHT | wx.LEFT | wx.TOP, 5), (self.te4, 1, wx.EXPAND | wx.LEFT | wx.RIGHT | wx.TOP, 5),
                    ])
        fgs.AddGrowableRow(8, 1)
        fgs.AddGrowableCol(1, 1)
        
         # StaticBoxSizer
        staticbox = wx.StaticBox(panel, id=wx.ID_ANY, label="New Dataset:")
        staticboxsizer = wx.StaticBoxSizer(staticbox, wx.VERTICAL)
        staticboxsizer.Add(fgs, 6, wx.EXPAND | wx.ALL, 5)
        

        hbox = wx.BoxSizer(wx.HORIZONTAL)
        okButton = wx.Button(panel, id=wx.ID_OK, label="Ok")
        CancelButton = wx.Button(panel, id=wx.ID_CANCEL, label="Cancel")
        hbox.Add(okButton, 0, wx.ALIGN_RIGHT | wx.ALL, 5)
        hbox.Add(CancelButton, 0, wx.ALIGN_RIGHT | wx.ALL, 5)



        
        note = wx.StaticText(panel,id=wxID_Note,label="Dataset Name Should Contain Only Two Characters!")
        notebox = wx.StaticBox(panel, id=wx.ID_ANY, label="Note:")
        noteboxsizer = wx.StaticBoxSizer(notebox, wx.VERTICAL)
        noteboxsizer.Add(note, 0, wx.EXPAND | wx.ALL, 5)
        
        vbox = wx.BoxSizer(wx.VERTICAL)
        vbox.Add(staticboxsizer, 0, wx.EXPAND | wx.ALL, 5)
        vbox.Add(hbox, 0, wx.ALIGN_RIGHT | wx.ALL, 5)
        vbox.Add(noteboxsizer, 0, wx.ALIGN_LEFT | wx.EXPAND | wx.ALL, 5)

        panel.SetSizer(vbox)
        
        pbox = wx.BoxSizer(wx.VERTICAL)
        pbox.Add(panel, 1, wx.EXPAND | wx.ALL)
        
        self.SetSizer(pbox)
        
        
    def GetValue(self):
        """
        return the dataset name, class, purpose, version
        """
        return (self.te1.Value, self.te2.Value, self.te3.Value, self.te4.Value)
    
    
class BrowsePopupWindow(wx.Panel):
    
    def __init__(self, parent, id=wx.ID_ANY):
        
        wx.Panel.__init__(self, parent, id)
        
        [wxID_TXTCTRL, wxID_BTN] = [wx.NewId() for _init_ctrls in range(2)]
        
        self.txtctrl = wx.TextCtrl(self, wxID_TXTCTRL)
        self.btn = wx.Button(self, wxID_BTN, "...", size=(20,20))
        
        sizer = wx.BoxSizer(wx.HORIZONTAL)
        #sizer.Add(self.txtctrl, 0, wx.EXPAND | wx.ALL, 0)
        sizer.Add(self.txtctrl, 1, wx.EXPAND | wx.ALL, 0)
        sizer.Add(self.btn, 0, wx.ALIGN_RIGHT | wx.ALL, 0)
        
        self.SetSizer(sizer)
        
        # when click, invoke clickevent function
        #self.Bind(wx.EVT_BUTTON, self.clickevent, self.btn)
        
    def SetValue(self, value):
        
        self.txtctrl.SetValue(value)
        
    
    def GetValue(self):
        
        return self.txtctrl.GetValue()
        
    def Enable(self):
        
        self.txtctrl.Enable()
        self.btn.Enable()
        
    def Disable(self):
        
        self.txtctrl.Disable()
        self.btn.Disable()
        
        
    
class CreateSDTMVarPage(wx.Panel):
    """
    Create AUI notebook page for SDTM variable level metadata
    """
    
    def __init__(self, parent, pageTitle):
        
        wx.Panel.__init__(self, parent)
        
        #self.SetBackgroundColour(wx.BLUE)
        
        self.StudyID = " "
        self.Domain = pageTitle
        
        self.splitter = wx.SplitterWindow(self, style=wx.SP_3D| wx.SP_LIVE_UPDATE)
        
       # top/down panel
        self.paneltop = wx.Panel(self.splitter, wx.ID_ANY)
        self.panelbottom = wx.Panel(self.splitter, wx.ID_ANY, style=wx.SUNKEN_BORDER)
        
        
        self.paneltop.SetBackgroundColour(wx.LIGHT_GREY)
        self.panelbottom.SetBackgroundColour(wx.LIGHT_GREY)
        self.panelbottom.SetBackgroundColour('light blue')
    
        #self.sizerx = wx.BoxSizer(wx.VERTICAL)
        #self.sizerx.Add(self.paneltop,20,flag=wx.EXPAND)
        #self.sizerx.AddSpacer(1,2)
        #self.sizerx.Add(self.panelbottom,10,flag=wx.EXPAND)
        #self.SetSizer(self.sizerx)
        
        self.lstCtrl = wx.ListCtrl(self.paneltop,style=wx.LC_REPORT)
        
        #parent.connDB.Execute("select * from SDTM_VARIABLES where [Domain] = '%s'" % pageTitle)
        
        #ID StudyID SeqOrder Class Domain BaseName Name Label Type Length BaseFormat Format Origin
        #SeqOrder, BaseName, Name, Label, Type, Length, BaseFormat, Format, Origin,
        #Role, CDISCNotes, Core, References, Comments, Applicable,
        #ValueListDefOID, ComputationMethodOID, SUPPQUAL
        
        #sqlstr = "select * from SDTM_VARIABLES where [Domain] = '%s'" % pageTitle
        sqlstr = "select \
                 SeqOrder, BaseName, Name, Label, Type, Length, BaseFormat, Format, Origin, \
                 Role, CDISCNotes, Core, References, Comments, Applicable, \
                 ValueListDefOID, ComputationMethodOID, SUPPQUAL \
                 from SDTM_VARIABLES where [Domain] = '%s'" % pageTitle
        
        parent.connDB.Execute(sqlstr)
        parent.connDB.FetchAll()
        self.headers = parent.connDB.GetHeaders()
        self.firstcol = parent.connDB.GetFirstCol()
        self.rows = parent.connDB.GetRows()
        
        #self.RowNum =len(self.rows)
        #
        ## If no observation, popup a messageDialog.
        #if not len(self.rows):
        #    dlg = wx.MessageDialog(parent, \
        #                    'No observations in %s' % pageTitle , \
        #                    'Warning', \
        #                    wx.OK | wx.ICON_INFORMATION \
        #                    )
        #    dlg.ShowModal()
        #    dlg.Destroy()
        #    
        #    return None
        #
        ## Else, add the observations.
        #else:
        
        for i,header in enumerate(self.headers):
            self.lstCtrl.InsertColumn(i, header[0])
            self.lstCtrl.SetColumnWidth(i, header[1])
    
    

        for i,firstcolname in enumerate(self.firstcol):
            self.lstCtrl.InsertStringItem(i, str(firstcolname))
            
            for j,col in enumerate(self.rows[i]):
                if j > 0:
                    if col == None:
                        self.lstCtrl.SetStringItem(i, j, u" ")
                    else:
                        try:
                            colstr = str(col)
                        except UnicodeEncodeError:
                            colstr = u" "
                            
                        self.lstCtrl.SetStringItem(i, j, colstr)
                        
            #if i % 2 == 0 :            
            #    self.item = self.lstCtrl.GetItem(i)
            #    self.item.SetBackgroundColour("light blue")
            #    self.lstCtrl.SetItem(item)
            
        self._SetItemBackgroundColor()
                
        self.lstCtrl.currentItem = 0
        
        # auto just the column width
        for i in range(self.lstCtrl.GetColumnCount()):
            self.lstCtrl.SetColumnWidth(i,wx.LIST_AUTOSIZE)
            
        self.sizertop = wx.BoxSizer(wx.VERTICAL)
        self.sizertop.Add(self.lstCtrl, 1, wx.EXPAND | wx.ALL, 3)
        self.paneltop.SetSizer(self.sizertop)
      
        
        # To do: panelbottom, SDTMVAR_edit
        self.SDTMVAR_edit = CreateSDTMVarEditor(self.panelbottom)
            
            
            
        self.splitter.SplitHorizontally(self.paneltop, self.panelbottom, sashPosition=0)
        
        # Set the default Sash Position
        self.splitter.SetSashPosition(250)
        self.splitter.SetMinimumPaneSize(50)
        
        self.splitterSizer = wx.BoxSizer(wx.VERTICAL)
        self.splitterSizer.Add(self.splitter, 1, wx.EXPAND | wx.ALL, 3)
        self.SetSizer(self.splitterSizer)

        self.Bind(wx.EVT_LIST_ITEM_SELECTED, self.EvtOnItemSelected, self.lstCtrl)
        self.Bind(wx.EVT_LIST_ITEM_ACTIVATED, self.EvtOnItemSelected, self.lstCtrl)


        #TO DO: Create a popupwindow for Format, ValueListDefOID, ComputationMethodOID
        self.Bind(wx.EVT_BUTTON, self.selectFormat, self.SDTMVAR_edit.te7.btn)
        self.Bind(wx.EVT_BUTTON, self.selectFormat, self.SDTMVAR_edit.te15.btn)
        self.Bind(wx.EVT_BUTTON, self.selectFormat, self.SDTMVAR_edit.te16.btn)
        
        self.Bind(wx.EVT_BUTTON, self.EvtOnSave, self.SDTMVAR_edit.save)
        self.Bind(wx.EVT_BUTTON, self.EvtOnDiscard, self.SDTMVAR_edit.discard)
        self.Bind(wx.EVT_BUTTON, self.EvtOnNew, self.SDTMVAR_edit.new)
        self.Bind(wx.EVT_BUTTON, self.EvtOnDelete, self.SDTMVAR_edit.delete)

        self.Bind(wx.EVT_BUTTON, self.closeWindow, self.SDTMVAR_edit.close)

    def _SetItemBackgroundColor(self):
        """
        Set the background color for item of the list ctrl
        """
        
        item_cnt = self.lstCtrl.GetItemCount()
        
        for i in range(item_cnt):
            if i % 2 == 0:
                self.lstCtrl.SetItemBackgroundColour(i, "light blue")
            else:
                self.lstCtrl.SetItemBackgroundColour(i, "white")

    def selectFormat(self, event):
        
        #dlg = wx.MessageDialog(self, 'Browse Format...', 'Information', wx.OK | wx.ICON_INFORMATION)
        #dlg.ShowModal()
        #dlg.Destroy()
        
        bf = BrowseFormat(self)
        bf.ShowModal()
        
        
    def closeWindow(self, event):
        
        #print self.GetParent()
        
        auiNB = self.GetParent()
        idx = auiNB.GetSelection()
        auiNB.DeletePage(idx)

    def getColumnText(self, index, col):
        item = self.lstCtrl.GetItem(index, col)
        return item.GetText()
    
    def EvtOnItemSelected(self, event):
        """
        when item in dataset listctrl is selected, show items in the SDTM_edit panel
        """
        currentItem = event.m_itemIndex
        
        self.lstCtrl.GetItemText(currentItem)
        
        self.SDTMVAR_edit.te1.SetValue("%s" % self.getColumnText(currentItem, 1))
        self.SDTMVAR_edit.te2.SetValue("%s" % self.getColumnText(currentItem, 2))
        self.SDTMVAR_edit.te3.SetValue("%s" % self.getColumnText(currentItem, 3))
        
        #self.SDTMVAR_edit.te4.SetValue("%s" % self.getColumnText(currentItem, 4))
        self.SDTMVAR_edit.te4.SetSelection(typelst.index(self.getColumnText(currentItem, 4)))
        
        self.SDTMVAR_edit.te5.SetValue("%s" % self.getColumnText(currentItem, 5))
        self.SDTMVAR_edit.te6.SetValue("%s" % self.getColumnText(currentItem, 6))
        
        self.SDTMVAR_edit.te7.SetValue("%s" % self.getColumnText(currentItem, 7))
        
        self.SDTMVAR_edit.te8.SetValue("%s" % self.getColumnText(currentItem, 8))
        self.SDTMVAR_edit.te9.SetValue("%s" % self.getColumnText(currentItem, 9))
        
        #CDISC Notes
        self.SDTMVAR_edit.te10.SetValue("%s" % self.getColumnText(currentItem, 10))
        
        #Core
        self.SDTMVAR_edit.te11.SetValue("%s" % self.getColumnText(currentItem, 11))
        
        #Reference
        self.SDTMVAR_edit.te12.SetValue("%s" % self.getColumnText(currentItem, 12))
        
        #Comments
        self.SDTMVAR_edit.te13.SetValue("%s" % self.getColumnText(currentItem, 13))
        
        #Applicable
        #print self.getColumnText(currentItem, 14)
        if self.getColumnText(currentItem, 14) == u"NA":
            self.SDTMVAR_edit.te14.SetValue(True)
        else:
            self.SDTMVAR_edit.te14.SetValue(False)
            
            
        #ValueListDefOID
        self.SDTMVAR_edit.te15.SetValue("%s" % self.getColumnText(currentItem, 15))
        
        #ComputationMethodOID
        self.SDTMVAR_edit.te16.SetValue("%s" % self.getColumnText(currentItem, 16))
        
        
        #SUPPQUAL
        if self.getColumnText(currentItem, 17) in ("Y" "YES" "Yes"):
            self.SDTMVAR_edit.te17.SetValue(True)
        else:
            self.SDTMVAR_edit.te17.SetValue(False)
            
        
            
        self.SDTMVAR_edit.save.Enable()
        self.SDTMVAR_edit.discard.Enable()
        self.SDTMVAR_edit.delete.Enable()
        
        
    def EvtOnSave(self, event):
        """
        Save record
        """
        
        idx = self.lstCtrl.GetFocusedItem()
        column_cnt = self.lstCtrl.GetColumnCount()
        
        #Save in database
        #varname = self.getColumnText(idx, 1)
        
        #StudyID SeqOrder Class Domain
        #BaseName Name Label Type Length BaseFormat Format Origin
        #Role CDISCNotes Core References Comments Applicable
        #ValueListDefOID ComputationMethodOID SUPPQUAL
        _BaseName = self.SDTMVAR_edit.te1.GetValue()
        _Name = self.SDTMVAR_edit.te2.GetValue()
        _Label = self.SDTMVAR_edit.te3.GetValue()
        
        _Type = typelst[self.SDTMVAR_edit.te4.GetSelection()]
        
        _Length = self.SDTMVAR_edit.te5.GetValue()
        _BaseFormat = self.SDTMVAR_edit.te6.GetValue()
        _Format = self.SDTMVAR_edit.te7.GetValue()
        _Origin = self.SDTMVAR_edit.te8.GetValue()
        _Role = self.SDTMVAR_edit.te9.GetValue()
        
        _CDISCNotes = self.SDTMVAR_edit.te10.GetValue()
        #_CDISCNotes = string.
        
        _Core = self.SDTMVAR_edit.te11.GetValue()
        _References = self.SDTMVAR_edit.te12.GetValue()
        _Comments = self.SDTMVAR_edit.te13.GetValue()
        
        if self.SDTMVAR_edit.te14.GetValue():
            _Applicable = "NA"
        else:
            _Applicable = " "
        
        _ValueListDefOID = self.SDTMVAR_edit.te15.GetValue()
        _ComputationMethodOID = self.SDTMVAR_edit.te16.GetValue()
        
        
        if self.SDTMVAR_edit.te17.GetValue():
            _SUPPQUAL = "Y"
        else:
            _SUPPQUAL = " "
            
        _StudyID = self.StudyID
        _Domain = self.Domain
        
        rows = (_Label, _Type, _Length, _BaseFormat, _Format, _Origin, \
                _Role, _CDISCNotes, _Core, _References, _Comments, _Applicable, \
                _ValueListDefOID, _ComputationMethodOID, _SUPPQUAL)

        #References = '%s'
        
        sql = "update SDTM_VARIABLES \
                set Label = '%s',\
                    Type = '%s',\
                    Length = %s,\
                    BaseFormat = '%s',\
                    Format = '%s',\
                    Origin = '%s',\
                    Role = '%s',\
                    Core = '%s',\
                    Comments = '%s',\
                    Applicable = '%s',\
                    ValueListDefOID = '%s',\
                    ComputationMethodOID = '%s',\
                    SUPPQUAL = '%s' \
                where [STUDYID] = '%s' and [DOMAIN] = '%s' and [BaseName] = '%s' and [Name] = '%s'" % \
                (_Label, _Type, _Length, _BaseFormat, _Format, _Origin, 
                _Role, _Core, _Comments, _Applicable, 
                _ValueListDefOID, _ComputationMethodOID, _SUPPQUAL,
                _StudyID, _Domain, _BaseName, _Name
                )
        try:
            
        #print sql
        
            self.GetParent().connDB.Execute(sql)
            self.GetParent().connDB.Commit()
    
            #Update list ctrl
            for i in range(column_cnt):
                if i > 2:
                    try:
                        self.lstCtrl.SetStringItem(idx,i, rows[i-3])
                        
                    except TypeError:
                        self.lstCtrl.SetStringItem(idx,i, str(rows[i-3]))
        
            dlg = wx.MessageDialog(self, 'Variable %s Saved!' % _Name , 'Information', wx.OK | wx.ICON_INFORMATION)
            dlg.ShowModal()
            dlg.Destroy()
    
        except adodbapi.adodbapi.DatabaseError:
            
            dlg = wx.MessageDialog(self, 'Database Error! Variable %s not Saved!' % _Name , 'Information', wx.OK | wx.ICON_INFORMATION)
            dlg.ShowModal()
            dlg.Destroy()
        
        
    def EvtOnDiscard(self, event):
        """
        Discard any changes made to current record
        """
        
        try:

            currentItem = self.lstCtrl.GetFocusedItem()
            
            self.SDTMVAR_edit.te1.SetValue("%s" % self.getColumnText(currentItem, 1))
            self.SDTMVAR_edit.te2.SetValue("%s" % self.getColumnText(currentItem, 2))
            self.SDTMVAR_edit.te3.SetValue("%s" % self.getColumnText(currentItem, 3))
            
            #self.SDTMVAR_edit.te4.SetValue("%s" % self.getColumnText(currentItem, 4))
            self.SDTMVAR_edit.te4.SetSelection(typelst.index(self.getColumnText(currentItem, 4)))
            
            self.SDTMVAR_edit.te5.SetValue("%s" % self.getColumnText(currentItem, 5))
            self.SDTMVAR_edit.te6.SetValue("%s" % self.getColumnText(currentItem, 6))
            
            self.SDTMVAR_edit.te7.SetValue("%s" % self.getColumnText(currentItem, 7))
            
            self.SDTMVAR_edit.te8.SetValue("%s" % self.getColumnText(currentItem, 8))
            self.SDTMVAR_edit.te9.SetValue("%s" % self.getColumnText(currentItem, 9))
            
            #CDISC Notes
            self.SDTMVAR_edit.te10.SetValue("%s" % self.getColumnText(currentItem, 10))
            
            #Core
            self.SDTMVAR_edit.te11.SetValue("%s" % self.getColumnText(currentItem, 11))
            
            #Reference
            self.SDTMVAR_edit.te12.SetValue("%s" % self.getColumnText(currentItem, 12))
            
            #Comments
            self.SDTMVAR_edit.te13.SetValue("%s" % self.getColumnText(currentItem, 13))
            
            #Applicable
            #print self.getColumnText(currentItem, 14)
            if self.getColumnText(currentItem, 14) == u"NA":
                self.SDTMVAR_edit.te14.SetValue(True)
            else:
                self.SDTMVAR_edit.te14.SetValue(False)
                
                
            #ValueListDefOID
            self.SDTMVAR_edit.te15.SetValue("%s" % self.getColumnText(currentItem, 15))
            
            #ComputationMethodOID
            self.SDTMVAR_edit.te16.SetValue("%s" % self.getColumnText(currentItem, 16))
            
            
            #SUPPQUAL
            if self.getColumnText(currentItem, 17) in ("Y" "YES" "Yes"):
                self.SDTMVAR_edit.te17.SetValue(True)
            else:
                self.SDTMVAR_edit.te17.SetValue(False)

        except:
            pass
        
        
    def EvtOnNew(self, event):
        """
        Create a New Dataset
        """
        
        
        # list of Dataset Name in list control
        Item_Cnt = self.lstCtrl.GetItemCount()
        VariableName = [self.getColumnText(i, 2) for i in range(Item_Cnt)]
        #dlg = wx.MessageDialog(self, 'VariableName: %s ' % VariableName , 'Information', wx.OK | wx.ICON_INFORMATION)
        #dlg.ShowModal()
        #dlg.Destroy()
        
        import re
        # other than character a-z A-Z
        pattern = "[^a-zA-Z]+"
        p = re.compile(pattern)


        dlg = NewVariable.NewVariable(self)
        #dlg.ShowModal()
        if dlg.ShowModal() == wx.ID_OK:
            #print dlg.GetValue()
            (BaseName, Name, Label, Type, Length) = dlg.GetValue()
            BaseName = BaseName.upper()
            Name = Name.upper()
            
            dlg.Destroy()
            
            try:
                if VariableName.index(Name) >= 0:
                    dlg = wx.MessageDialog(self, 'Variable %s Already Exists! Please Choose Another Name' % Name , 'Information', wx.OK | wx.ICON_INFORMATION)
                    dlg.ShowModal()
                    dlg.Destroy()
            except ValueError:
                pass
            
                #    except ValueError:
                #        if p.search(domain) is None and len(domain) == 2:
                #            #create domain
                #            
                #            #First insert into the list ctrl
                #            self.lstCtrl.InsertStringItem(Item_Cnt + 1, domain)
                #            self.lstCtrl.SetStringItem(Item_Cnt, 1, "New Domain")
                #            self.lstCtrl.SetStringItem(Item_Cnt, 2, class1)
                #            self.lstCtrl.SetStringItem(Item_Cnt, 3, "One record per visit per subject")
                #            self.lstCtrl.SetStringItem(Item_Cnt, 4, purpose)
                #            self.lstCtrl.SetStringItem(Item_Cnt, 5, "STUDYID, USUBJID, ")
                #            self.lstCtrl.SetStringItem(Item_Cnt, 6, "Study Specific.")
                #            self.lstCtrl.SetStringItem(Item_Cnt, 7, version)
                #            
                #            #Then insert into database
                #            sql = "insert into SDTM_DATASETS (DATASET, DESCRIPTION, CLASS, STRUCTURE, PURPOSE, KEYVAR, COMMENTS, VERSION) \
                #                  values ('%s', 'New Domain', '%s', 'One record per visit per subject', '%s', 'STUDYID, USUBJID, ', 'Study Specific.', '%s') " % (domain, class1, purpose, version)
                #            self.connDB.Execute(sql)
                #            self.connDB.Commit()
                #            #At last, select this new row in list ctrl
                #            self._SetItemBackgroundColor()
                #            self.lstCtrl.Select(Item_Cnt)
                #            self.lstCtrl.Focus(Item_Cnt)
                #            
                #            dlg = wx.MessageDialog(self, 'Dataset Name %s created!' % domain , 'Information', wx.OK | wx.ICON_INFORMATION)
                #            dlg.ShowModal()
                #            dlg.Destroy()
                #
                #        else:
                #            dlg = wx.MessageDialog(self, 'Dataset Name %s should only Contain two characters!' % domain , 'Information', wx.OK | wx.ICON_INFORMATION)
                #            dlg.ShowModal()
                #            dlg.Destroy()
                    

        
        
    def EvtOnDelete(self, event):
        """
        Delete record
        """
        
        idx = self.lstCtrl.GetFocusedItem()
        item_cnt = self.lstCtrl.GetItemCount()
        
        #Delete in database
        _StudyID = self.StudyID
        _Domain = self.Domain
        
        _BaseName = self.SDTMVAR_edit.te1.GetValue()
        _Name = self.SDTMVAR_edit.te2.GetValue()
        
        
        sql = "delete * from SDTM_VARIABLES \
               where [STUDYID] = '%s' and [DOMAIN] = '%s' and [BaseName] = '%s' and [Name] = '%s'" % \
               (_StudyID, _Domain, _BaseName, _Name)
        
        
        try:
            
            #print sql
        
            self.GetParent().connDB.Execute(sql)
            self.GetParent().connDB.Commit()
    
            self.lstCtrl.DeleteItem(idx)
        
            if idx < item_cnt:
                self.lstCtrl.Select(idx)
            else:
                self.lstCtrl.Select(idx - 1)
                
            # Set Background Color again
            self._SetItemBackgroundColor()
    
        except adodbapi.adodbapi.DatabaseError:
            
            dlg = wx.MessageDialog(self, 'Database Error! Variable %s not Deleted!' % _Name , 'Information', wx.OK | wx.ICON_INFORMATION)
            dlg.ShowModal()
            dlg.Destroy()
            
        
        
        
        

class CreateSDTMVarEditor:
    """
    Create some StaticText and TextCtrl for Editing SDTM Variable level metadata
    """
    
    def __init__(self, parent):
        
        [wxID_BaseName,
         wxID_Name,
         wxID_Label,
         wxID_Type,
         wxID_Length,
         wxID_BaseFormat,
         wxID_Format,
         wxID_Origin,
         wxID_Role,
         wxID_CDISCNotes,
         wxID_Core,
         wxID_References,
         wxID_Comments,
         wxID_Applicable,
         wxID_ValueListDefOID,
         wxID_ComputationMethodOID,
         wxID_SUPPQUAL,
         
         wxID_Save,
         wxID_Discard,
         wxID_New,
         wxID_Close,
         wxID_Delete
        ] = [wx.NewId() for _init_ctrls in range(22)]
        
        #Class list    
        #classlst = connDB().classlst
        
        self.lb1 = wx.StaticText(parent, wx.ID_ANY, "Base Name:")
        self.te1 = wx.TextCtrl(parent, wxID_BaseName)
        self.te1.SetMaxLength(8)
        self.te1.Disable()
        
        self.lb2 = wx.StaticText(parent, wx.ID_ANY, "Name:")
        self.te2 = wx.TextCtrl(parent, wxID_Name)
        self.te2.SetMaxLength(8)
        self.te2.Disable()
        
        self.lb3 = wx.StaticText(parent, wx.ID_ANY, "Label:")
        self.te3 = wx.TextCtrl(parent, wxID_Label)
        self.te3.SetMaxLength(256)
        
        
        self.lb4 = wx.StaticText(parent, wx.ID_ANY, "Type:")
        #self.te4 = wx.TextCtrl(parent, wxID_Type)
        self.te4 = wx.Choice(parent, wxID_Type, choices = typelst)
        
        self.lb5 = wx.StaticText(parent, wx.ID_ANY, "Length:")
        self.te5 = wx.TextCtrl(parent, wxID_Length)
        
        self.lb6 = wx.StaticText(parent, wx.ID_ANY, "Base Format:")
        self.te6 = wx.TextCtrl(parent, wxID_BaseFormat)
        
        self.lb7 = wx.StaticText(parent, wx.ID_ANY, "Format:")
        #self.te7 = wx.TextCtrl(parent, wxID_Format)
        self.te7 = BrowsePopupWindow(parent, wxID_Format)
        
        originlst = ["CRF", "Derived", "Sponsor Defined", "CRF or Derived", "CRF or Sponsor Defined"]
        self.lb8 = wx.StaticText(parent, wx.ID_ANY, "Origin:")
        #self.te8 = wx.TextCtrl(parent, wxID_Origin)
        self.te8 = wx.ComboBox(parent, wxID_Origin, "", choices=originlst)
        
        rolelst = ["Identifier", "Topic", "Synonym Qualifier", "Grouping Qualifier", "Record Qualifier", "Variable Qualifier", "Timing"]
        self.lb9 = wx.StaticText(parent, wx.ID_ANY, "Role:")
        #self.te9 = wx.TextCtrl(parent, wxID_Role)
        self.te9 = wx.ComboBox(parent, wxID_Role, "", choices=rolelst)
        
        
        self.lb10 = wx.StaticText(parent, wx.ID_ANY, "CDISC Notes:")
        self.te10 = wx.TextCtrl(parent, wxID_CDISCNotes, style=wx.TE_MULTILINE)
        self.te10.SetEditable(False)
        #self.te10.Disable()
        
        corelst = ["Req", "Exp", "Perm"]
        self.lb11 = wx.StaticText(parent, wx.ID_ANY, "Core:")
        #self.te11 = wx.TextCtrl(parent, wxID_Core)
        self.te11 = wx.ComboBox(parent, wxID_Core, "", choices=corelst)
        
        self.lb12 = wx.StaticText(parent, wx.ID_ANY, "References:")
        self.te12 = wx.TextCtrl(parent, wxID_References)
        
        self.lb13 = wx.StaticText(parent, wx.ID_ANY, "Comments:")
        self.te13 = wx.TextCtrl(parent, wxID_Comments, style=wx.TE_MULTILINE)
        
        self.lb14 = wx.StaticText(parent, wx.ID_ANY, "Applicable:")
        #self.te14 = wx.TextCtrl(parent, wxID_Applicable)
        self.te14 = wx.CheckBox(parent, wxID_Applicable, "N/A")
        
        self.lb15 = wx.StaticText(parent, wx.ID_ANY, "ValueListDefOID:")
        #self.te15 = wx.TextCtrl(parent, wxID_ValueListDefOID)
        self.te15 = BrowsePopupWindow(parent, wxID_ValueListDefOID)
        
        self.lb16 = wx.StaticText(parent, wx.ID_ANY, "ComputationMethodOID:")
        #self.te16 = wx.TextCtrl(parent, wxID_ComputationMethodOID)
        self.te16 = BrowsePopupWindow(parent, wxID_ComputationMethodOID)
        
        self.lb17 = wx.StaticText(parent, wx.ID_ANY, "SUPPQUAL:")
        #self.te17 = wx.TextCtrl(parent, wxID_SUPPQUAL)
        self.te17 = wx.CheckBox(parent, wxID_SUPPQUAL, "Yes")
        
        
        
        # Save, Discard,
        self.save = wx.Button(parent,wxID_Save, "&Save")
        self.discard = wx.Button(parent,wxID_Discard, "Discard")
        self.new = wx.Button(parent,wxID_New, "&New...")
        self.delete = wx.Button(parent,wxID_Delete, "Delete")
        self.close = wx.Button(parent,wxID_Close, "Close")
        
        self.save.Disable()
        self.discard.Disable()
        self.delete.Disable()
        
        
        
        #boxsizer
        hbox = wx.BoxSizer(wx.HORIZONTAL)
        #Base Name
        hbox.Add(self.lb1, 0, wx.ALIGN_RIGHT | wx.ALL, 5)
        hbox.Add(self.te1, 0, wx.ALL, 5)
        #Name
        hbox.Add(self.lb2, 0, wx.ALIGN_RIGHT | wx.ALL, 5)
        hbox.Add(self.te2, 0, wx.ALL, 5)
        #Label
        hbox.Add(self.lb3, 0, wx.ALIGN_RIGHT | wx.ALL, 5)
        hbox.Add(self.te3, 1, wx.ALL, 5)
        
        
        ##boxsizer 2
        #hbox2 = wx.BoxSizer(wx.HORIZONTAL)
        ##Type
        #hbox2.Add(self.lb4, 0, wx.ALIGN_RIGHT | wx.ALL, 5)
        #hbox2.Add(self.te4, 0, wx.ALL, 5)
        ##Length
        #hbox2.Add(self.lb5, 0, wx.ALIGN_RIGHT | wx.ALL,5)
        #hbox2.Add(self.te5, 0, wx.ALL,5)
        ##Base Format
        #hbox2.Add(self.lb6, 0, wx.ALIGN_RIGHT | wx.ALL,5)
        #hbox2.Add(self.te6, 0, wx.ALL,5)
        ##Format
        #hbox2.Add(self.lb7, 0, wx.ALIGN_RIGHT | wx.ALL,5)
        #hbox2.Add(self.te7, 0, wx.ALL,5)
        ##Origin
        #hbox2.Add(self.lb8, 0, wx.ALIGN_RIGHT | wx.ALL,5)
        #hbox2.Add(self.te8, 0, wx.ALL,5)
        ##Role
        #hbox2.Add(self.lb9, 0, wx.ALIGN_RIGHT | wx.ALL,5)
        #hbox2.Add(self.te9, 0, wx.ALL,5)
        
        #boxsizer 3
        hbox3 = wx.BoxSizer(wx.HORIZONTAL)
        #CDISC Notes
        hbox3.Add(self.lb10, 0, wx.ALIGN_RIGHT | wx.ALL, 5)
        hbox3.Add(self.te10, 1, wx.EXPAND | wx.ALL, 5)
        
        
        ##boxsizer 4
        #hbox4 = wx.BoxSizer(wx.HORIZONTAL)
        ##Core
        #hbox4.Add(self.lb11, 0, wx.ALIGN_RIGHT | wx.ALL, 5)
        #hbox4.Add(self.te11, 1, wx.ALL, 5)
        ##Reference
        #hbox4.Add(self.lb12, 0, wx.ALIGN_RIGHT | wx.ALL, 5)
        #hbox4.Add(self.te12, 1, wx.ALL, 5)
        ##Applicable
        #hbox4.Add(self.lb14, 0, wx.ALIGN_RIGHT | wx.ALL, 5)
        #hbox4.Add(self.te14, 1, wx.ALL, 5)
        ##SUPPQUAL
        #hbox4.Add(self.lb17, 0, wx.ALIGN_RIGHT | wx.ALL, 5)
        #hbox4.Add(self.te17, 1, wx.ALL, 5)
        
        #FlexGridSizer
        fgs = wx.FlexGridSizer(3,10,5,5)
        fgs.AddMany([(self.lb4, 0, wx.ALIGN_RIGHT | wx.LEFT | wx.TOP, 5), (self.te4, 1, wx.LEFT | wx.TOP, 5),
                     (self.lb5, 0, wx.ALIGN_RIGHT | wx.LEFT | wx.TOP, 5), (self.te5, 1, wx.LEFT | wx.TOP, 5),
                     (self.lb6, 0, wx.ALIGN_RIGHT | wx.LEFT | wx.TOP, 5), (self.te6, 1, wx.LEFT | wx.TOP, 5),
                     (self.lb7, 0, wx.ALIGN_RIGHT | wx.LEFT | wx.TOP, 5), (self.te7, 1, wx.LEFT | wx.TOP, 5),
                     (self.lb8, 0, wx.ALIGN_RIGHT | wx.LEFT | wx.TOP, 5), (self.te8, 1, wx.LEFT | wx.TOP, 5),
                     
                     (self.lb9, 0, wx.ALIGN_RIGHT | wx.LEFT | wx.TOP, 5), (self.te9, 1, wx.LEFT | wx.TOP, 5),
                     (self.lb11, 0, wx.ALIGN_RIGHT | wx.LEFT | wx.TOP, 5), (self.te11, 1, wx.LEFT | wx.TOP, 5),
                     (self.lb12, 0, wx.ALIGN_RIGHT | wx.LEFT | wx.TOP, 5), (self.te12, 1, wx.LEFT | wx.TOP, 5),
                     (self.lb14, 0, wx.ALIGN_RIGHT | wx.LEFT | wx.TOP, 5), (self.te14, 1, wx.LEFT | wx.TOP, 5),
                     (self.lb17, 0, wx.ALIGN_RIGHT | wx.LEFT | wx.TOP, 5), (self.te17, 1, wx.LEFT | wx.TOP, 5),
                    ])
        fgs.AddGrowableRow(1, 0)
        fgs.AddGrowableCol(6, 0)
        
        #boxsizer 5
        hbox5 = wx.BoxSizer(wx.HORIZONTAL)
        #ValueListDefOID
        hbox5.Add(self.lb15, 0, wx.ALIGN_RIGHT | wx.ALL, 5)
        hbox5.Add(self.te15, 1, wx.EXPAND | wx.ALL, 5)
        #ComputationMethodOID
        hbox5.Add(self.lb16, 0, wx.ALIGN_RIGHT | wx.ALL, 5)
        hbox5.Add(self.te16, 1, wx.EXPAND | wx.ALL, 5)
        
        #boxsizer 6
        hbox6 = wx.BoxSizer(wx.HORIZONTAL)
        #Comments
        hbox6.Add(self.lb13, 0, wx.ALIGN_RIGHT | wx.ALL, 5)
        hbox6.Add(self.te13, 1, wx.EXPAND | wx.ALL, 5)
        
        #static line
        staticline = wx.StaticLine(parent)
        
        
        #boxsizer
        hbox_btn = wx.BoxSizer(wx.HORIZONTAL)
        hbox_btn.Add(self.save, 0, wx.ALIGN_RIGHT | wx.ALL, 5)
        hbox_btn.Add(self.discard, 0, wx.ALIGN_RIGHT | wx.ALL, 5)
        hbox_btn.Add(self.new, 0, wx.ALIGN_RIGHT | wx.ALL, 5)
        hbox_btn.Add(self.delete, 0, wx.ALIGN_RIGHT | wx.ALL, 5)
        hbox_btn.Add(self.close, 0, wx.ALIGN_RIGHT | wx.ALL, 5)
        
        staticbox = wx.StaticBox(parent, id=wx.ID_ANY, label="Edit Variable Metadata:")
        
        staticboxsizer = wx.StaticBoxSizer(staticbox, wx.VERTICAL)
        staticboxsizer.Add(hbox, 0, wx.ALIGN_LEFT | wx.EXPAND | wx.ALL, 0)
        
        staticboxsizer.Add(fgs, 0, wx.ALIGN_LEFT | wx.ALL, 0)
        #staticboxsizer.Add(hbox2, 0, wx.ALIGN_LEFT | wx.ALL, 0)
        #staticboxsizer.Add(hbox4, 0, wx.ALIGN_LEFT | wx.ALL, 0)
        
        staticboxsizer.Add(staticline, 0, wx.ALIGN_LEFT | wx.EXPAND | wx.ALL, 5)
        
        
        staticboxsizer.Add(hbox5, 0, wx.ALIGN_LEFT | wx.EXPAND | wx.ALL, 0)
        
        staticboxsizer.Add(hbox3, 1, wx.ALIGN_LEFT | wx.EXPAND | wx.ALL, 0)
        staticboxsizer.Add(hbox6, 1, wx.ALIGN_LEFT | wx.EXPAND | wx.ALL, 0)
        #staticboxsizer.Add(fgs, 6, wx.EXPAND | wx.ALL, 5)
        
        #vertical boxsizer
        vbox = wx.BoxSizer(wx.VERTICAL)
        vbox.Add(staticboxsizer, 6, wx.EXPAND | wx.ALL, 5)
        vbox.Add(hbox_btn, 1, wx.ALIGN_RIGHT | wx.ALL, 5)
        
        parent.SetSizer(vbox)
